package net.comm.group.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class GPictureServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from GPicture where GPicID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into GPicture ( gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				") values (?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update GPicture set galbumID=?,  picName=?,  isHead=?, upCnt=?, downCnt=?, userID=?,  mtime=? " + 
				"where GPicID=?";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpUpdownSql()
	{
		return "update GPicture set upCnt=?, downCnt=? " + 
				"where GPicID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture where GPicID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture");
		
		sb.append(" where UserID=? ");
				
		return sb.toString();
	}
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByPrevSql()
	{
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture");
		
		sb.append(" where gpicID < ? and galbumID=? Order by gpicID desc limit 1");
				
		return sb.toString();
	}
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByNextSql()
	{
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture");
		
		sb.append(" where gpicID >= ? and galbumID=?  Order by gpicID limit 2");
				
		return sb.toString();
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByJoinUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture");
		
		sb.append(" where GAlbumID in (select GAlbumID from GAlbum where GroupID in ( select GroupID from GMember where userID=?)) " +
				" group by gAlbumID Order by mTime ");
				
		return sb.toString();
	}

	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByAlbumIDSql()
	{
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture");
		
		sb.append(" where gAlbumID=?  Order by MTime ");
				
		return sb.toString();
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture ");
		
		sb.append(" where gAlbumID in (select GAlbumID from GAlbum where GroupID=? ) order by gpicID desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture ");
				
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPage2Sql()
	{
	
		StringBuffer sb =  new StringBuffer("select gpicID,  galbumID,  picName,  isHead, upCnt, downCnt, userID,  mtime " +
				" from GPicture ");
		
		sb.append(" where gAlbumID=? ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from GPicture " +
				" where gAlbumID in (select GAlbumID from GAlbum where GroupID=? ) ";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from GPicture where gAlbumID=? ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from GPicture " ;
	}
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
